﻿using B.S.InOut.Domain;
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace B.S.InOut.Data.Tranmanage
{
    using Dapper;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Threading.Tasks;

    namespace YourNamespace.Repositories
    {
        public class TranmangeRepository : ITranmangeRepository
        {
            // 数据库连接字符串
            private readonly string _connectionString;

            // 构造函数，接收数据库连接字符串
            public TranmangeRepository(string connectionString)
            {
                _connectionString = connectionString;
            }

            static List<string> SplitData(string data)
            {
                data = data.Trim('{', '}');
                return data.Split(new[] { "},{", "}" }, StringSplitOptions.RemoveEmptyEntries).ToList();
            }
            /// <summary>
            /// 插入移库记录的异步方法
            /// </summary>
            /// <param name="product">要插入的移库实体对象</param>
            /// <returns>返回插入记录的主键值（自增 ID）</returns>
            public async Task<int> InsertTranMenageAsync(Tranmanagement product)
            {
                var pIds = SplitData(product.PId);
                var batches = SplitData(product.Batch);
                var specifications = SplitData(product.Specifications);
                var quantities = SplitData(product.Quantity);
                var originalLocations = SplitData(product.Originallocation);
                var newLocations = SplitData(product.Newlocation);
                var productNums = SplitData(product.ProductNum);
                var productNames = SplitData(product.ProductName);
                var prices = SplitData(product.Price);

                // 用于存储最后插入记录的 ID11
                int lastInsertedId = 0;

               
                using (var connection = new SqlConnection(_connectionString))
                {
                    try
                    {
                        // SQL 插入语句，将 Tranmanagement 对象的属性值插入到数据库表中
                        var sql = @"
                    INSERT INTO Tranmanagement (PId, TransferNum, AssociatedNum, Batch, Specifications, Quantity, Warehouse, Originallocation, Newlocation, Transfer, TranCreator, TranCreationTime, TranReviewer, TranAuditTime, TranAuditstatus, TranModifiedBy, TranChangeTime, Rember, IsDelete, ShenRember, ProductNum, ProductName,Price)
                    VALUES (@PId, @TransferNum, @AssociatedNum, @Batch, @Specifications, @Quantity, @Warehouse, @Originallocation, @Newlocation, @Transfer, @TranCreator, @TranCreationTime, @TranReviewer, @TranAuditTime, @TranAuditstatus, @TranModifiedBy, @TranChangeTime, @Rember, @IsDelete, @ShenRember, @ProductNum, @ProductName,@Price);
                    SELECT CAST(SCOPE_IDENTITY() as int);
                ";

                        for (int i = 0; i < pIds.Count; i++)
                        {
                            var record = new Tranmanagement
                            {
                                Id = product.Id,
                                PId = pIds[i],
                                TransferNum = product.TransferNum,
                                AssociatedNum = product.AssociatedNum,
                                Batch = batches[i],
                                Specifications = specifications[i],
                                Quantity = quantities[i],
                                Warehouse = product.Warehouse,
                                Originallocation = originalLocations[i],
                                Newlocation = newLocations[i],
                                Transfer = product.Transfer,
                                TranCreator = product.TranCreator,
                                TranCreationTime = (product.TranCreationTime),
                                TranReviewer = product.TranReviewer,
                                TranAuditstatus = product.TranAuditstatus,
                                TranModifiedBy = product.TranModifiedBy,
                                TranChangeTime = product.TranChangeTime,
                                Rember = product.Rember,
                                IsDelete = product.IsDelete,
                                ShenRember = product.ShenRember,
                                ProductNum = productNums[i],
                                ProductName = productNames[i],
                                Price= prices[i]
                            };
                            lastInsertedId = await connection.ExecuteScalarAsync<int>(sql,record);
                        }

                    }
                    catch (Exception ex)
                    {
                        // 处理异常，可根据实际需求进行日志记录或其他操作
                        Console.WriteLine($"插入数据时发生错误: {ex.Message}");
                        // 发生异常时返回 -1 表示插入失败
                        return -1;
                    }

                    // 执行插入操作并返回插入记录的自增 ID
                    return lastInsertedId;
                }
            }

            /// <summary>
            /// 根据移库 ID 获取移库记录的异步方法
            /// </summary>
            /// <param name="id">移库记录的 ID</param>
            /// <returns>返回对应的移库实体对象，如果未找到则返回 null</returns>
            public async Task<Tranmanagement> GetTranMenageByIdAsync(int id)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // SQL 查询语句，根据 ID 查询移库记录
                    var sql = "SELECT * FROM Tranmanagement WHERE Id = @Id";
                    // 执行查询操作并返回结果
                    return await connection.QueryFirstOrDefaultAsync<Tranmanagement>(sql, new { Id = id });
                }
            }

            /// <summary>
            /// 获取所有移库记录的异步方法
            /// </summary>
            /// <returns>返回所有移库记录的集合</returns>
            public async Task<IEnumerable<Tranmanagement>> GetAllTranMenageAsync()
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // SQL 查询语句，查询所有移库记录
                    var sql = "SELECT * FROM Tranmanagement";
                    // 执行查询操作并返回结果集合
                    return await connection.QueryAsync<Tranmanagement>(sql);
                }
            }

            /// <summary>
            /// 更新移库记录的异步方法
            /// </summary>
            /// <param name="product">要更新的移库实体对象</param>
            /// <returns>返回受影响的行数</returns>
            public async Task<int> UpdateTranMenageAsync(Tranmanagement product)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                 
                        // SQL 更新语句，根据 Tranmanagement 对象的属性值更新数据库表中的记录
                        var sql = @"
                    UPDATE Tranmanagement
                    SET PId = @PId, TransferNum = @TransferNum, AssociatedNum = @AssociatedNum, Batch = @Batch, Specifications = @Specifications, Quantity = @Quantity, Warehouse = @Warehouse, Originallocation = @Originallocation, Newlocation = @Newlocation, Transfer = @Transfer, TranCreator = @TranCreator, TranCreationTime = @TranCreationTime, TranReviewer = @TranReviewer, TranAuditstatus = @TranAuditstatus, TranModifiedBy = @TranModifiedBy, TranChangeTime = @TranChangeTime, Rember = @Rember, IsDelete = @IsDelete, ShenRember = @ShenRember, ProductNum = @ProductNum, ProductName = @ProductName,Price=@price
                    WHERE Id = @Id;
                ";
                    // 执行更新操作并返回受影响的行数
                    return await connection.ExecuteAsync(sql, product);

                }
        
            }

          

            /// <summary>
            /// 根据移库 ID 删除移库记录的异步方法
            /// </summary>
            /// <param name="id">要删除的移库记录的 ID</param>
            /// <returns>返回受影响的行数</returns>
            public async Task<int> DeleteTranMenageAsync(int id)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // SQL 删除语句，根据 ID 删除移库记录
                    var sql = "DELETE FROM Tranmanagement WHERE Id = @Id";
                    // 执行删除操作并返回受影响的行数
                    return await connection.ExecuteAsync(sql, new { Id = id });
                }
            }

            /// <summary>
            /// 多条件分页查询移库记录的异步方法
            /// </summary>
            /// <param name="Outboundnumber">移库订单号（可空，作为查询条件）</param>
            /// <param name="Outbound">移库类型（可空，作为查询条件）</param>
            /// <param name="OutAuditstatus">审核状态（可空，作为查询条件）</param>
            /// <param name="pageindex">页码</param>
            /// <param name="pageSize">每页显示的记录数</param>
            /// <returns>返回符合条件的分页后的移库记录集合</returns>
            public async Task<IEnumerable<Tranmanagement>> GetTranMenageByConditionsAsync(string? TransferNum, int? Transfer, int? TranAuditstatus, int pageindex, int pagesize)
            {
                using (var connection = new SqlConnection(_connectionString))
               {
                    // SQL 查询语句，根据条件进行分页查询
                    var sql = @" SELECT * FROM Tranmanagement WHERE 1=1; ";
                    var parameters = new DynamicParameters();
                    // 如果产品名称不为空，添加名称查询条件
                    if (!string.IsNullOrEmpty(TransferNum))
                    {
                        sql += " AND TransferNum like @TransferNum";
                        parameters.Add("@TransferNum", $"%{TransferNum}%");
                    }

                    // 如果最低价格不为空，添加最低价格查询条件
                    if (Transfer != null)
                    {
                        sql += " AND Transfer = @Transfer";
                        parameters.Add("@Transfer", Transfer);
                    }

                    // 如果最高价格不为空，添加最高价格查询条件
                    if (TranAuditstatus != null)
                    {
                        sql += " AND TranAuditstatus = @TranAuditstatus";
                        parameters.Add("@TranAuditstatus", $"{TranAuditstatus}");
                    }
                    // 添加分页逻辑
                    var offset = (pageindex - 1) * pagesize;
                    parameters.Add("@Offset", offset);
                    parameters.Add("@PageSize", pagesize);
                    parameters.Add("@pageindex", pageindex);
                    // 执行查询操作并返回结果集合
                    return await connection.QueryAsync<Tranmanagement>(sql, parameters);
                }
            }

            /// <summary>
            /// 批量删除移库记录的异步方法
            /// </summary>
            /// <param name="productIds">要删除的移库记录的 ID 列表</param>
            /// <returns>返回受影响的行数</returns>
            public async Task<int> DeleteTranMenageAsyncd(IEnumerable<int> productIds)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // SQL 删除语句，根据 ID 列表批量删除移库记录
                    var sql = "DELETE FROM Tranmanagement WHERE Id IN @ProductIds";
                    // 执行删除操作并返回受影响的行数
                    return await connection.ExecuteAsync(sql, new { ProductIds = productIds });
                }
            }

            /// <summary>
            /// 联合查询 Tranmanagement 和 ProductModel 表的异步方法
            /// </summary>
            /// <param name="Outboundnumber">移库订单号（可空，作为查询条件）</param>
            /// <param name="pageindex">页码</param>
            /// <param name="pageSize">每页显示的记录数</param>
            /// <returns>返回联合查询结果的移库记录集合</returns>
            public async Task<IEnumerable<Tranmanagement>> GetTranmanagementWithProductModelAsync(string? Outboundnumber, int pageindex, int pageSize)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // 假设 Tranmanagement 表和 ProductModel 表通过 PId 和 ProductId 关联
                    var sql = @"
                    SELECT tm.*, pm.* 
                    FROM Tranmanagement tm
                    JOIN ProductModel pm ON tm.PId = pm.ProductId
                    WHERE (@Outboundnumber IS NULL OR tm.TransferNum = @Outboundnumber)
                    ORDER BY tm.Id
                    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
                ";
                    var parameters = new
                    {
                        Outboundnumber,
                        Offset = (pageindex - 1) * pageSize,
                        PageSize = pageSize
                    };
                    // 执行查询操作并返回结果集合
                    return await connection.QueryAsync<Tranmanagement>(sql, parameters);
                }
            }

            /// <summary>
            /// 根据出库 ID 查询 Tranmanagement 和 ProductModel 两表关联数据的异步方法
            /// </summary>
            /// <param name="Id">出库 ID</param>
            /// <returns>返回查询到的移库实体对象，如果未找到则返回 null</returns>
            public async Task<Tranmanagement> GetTranmanagementAndProductModelByIdAsync(int Id)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // 假设 Tranmanagement 表和 ProductModel 表通过 PId 和 ProductId 关联
                    var sql = @"
                    SELECT tm.*, pm.* 
                    FROM Tranmanagement tm
                    JOIN ProductModel pm ON tm.PId = pm.ProductId
                    WHERE tm.Id = @Id;
                ";
                    // 执行查询操作并返回结果
                    return await connection.QueryFirstOrDefaultAsync<Tranmanagement>(sql, new { Id });
                }
            }

            /// <summary>
            /// 批量删除移库记录并返回受影响行数（方法名可能有误，这里按接口实现）的异步方法
            /// </summary>
            /// <param name="productIds">要删除的移库记录的 ID 列表</param>
            /// <returns>返回受影响的行数</returns>
            public async Task<int> GetTranMenageAsyncdIds(IEnumerable<int> productIds)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // SQL 查询语句，统计要删除的记录数
                    var sql = "SELECT COUNT(*) FROM Tranmanagement WHERE Id IN @ProductIds";
                    // 执行查询操作并返回统计结果（受影响的行数）
                    return await connection.ExecuteScalarAsync<int>(sql, new { ProductIds = productIds });
                }
            }

            /// <summary>
            /// 根据移库订单号查询移库记录的异步方法
            /// </summary>
            /// <param name="outboundNumber">移库订单号</param>
            /// <returns>返回符合条件的移库记录集合</returns>
            public async Task<IEnumerable<Tranmanagement>> GetTranmanagementByOutboundNumberAsync(string outboundNumber)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    // SQL 查询语句，根据移库订单号查询移库记录
                    var sql = "SELECT * FROM Tranmanagement WHERE TransferNum = @outboundNumber";
                    // 执行查询操作并返回结果集合
                    return await connection.QueryAsync<Tranmanagement>(sql, new { outboundNumber });
                }
            }
        }
    }
}
